package db;

import model.*;

import java.sql.*;
import java.util.ArrayList;

/**
 * DBEmployee.java
 * @author kbh
 * @version 5. oktober 2006, 2011.02.16
 *
 */
public class DBEmployee{
    private  Connection con;
    private DBPerson dbPerson = new DBPerson();
    
    /** Creates a new instance of DBEmployee */
    public DBEmployee() {
      con = DBConnection.getInstance().getDBcon();
    }
    
  //Implements the methods from the interface
    // get all EmployeeGroups
    public ArrayList<Employee> getAllEmployeesByStatus(String _status, boolean _retriveAssociation)
    {
        return miscWhere(" status = '" + _status + "' AND cpr != 'admin'", _retriveAssociation);
    }
   
    //get one EmployeeGroup having the group type
    public Employee getEmployeeCpr(String _cpr, boolean _retriveAssociation)
    {   String wClause = "  cpr = '" + _cpr + "'";
        return singleWhere(wClause, _retriveAssociation);
    }
    //find one EmployeeGroup having the id
    public Employee getEmployeeName(String _name, boolean _retriveAssociation)
    {
        String wClause = "name like '%" + _name + "%'";
        System.out.println("SearchEmployee " + wClause);
        return singleWhere(wClause, _retriveAssociation);
    }
    
    public Employee getEmployeeLname(String _lname, boolean _retriveAssociation)
    {
        String wClause = "lname like '%" + _lname + "%'";
        System.out.println("SearchEmployee " + wClause);
        return singleWhere(wClause, _retriveAssociation);
    }
  
     //insert a new Employee group
    public int insertEmployee(Employee _employee) throws Exception
    {  
       int rc = -1;
	   String query="INSERT INTO Employee(cpr, password, accessType, status)  VALUES('"+
			   _employee.getCpr() + "','" +
			   _employee.getPassword() + "','" +
	   		   _employee.getAccessType() + "','" +
	   		   _employee.getStatus() + "')";
                     

       System.out.println("insert : " + query);
      try{ // insert new employee +  dependent
          Statement stmt = con.createStatement();
          stmt.setQueryTimeout(5);
     	  rc = stmt.executeUpdate(query);
          stmt.close();
      }//end try
       catch(SQLException ex){
          System.out.println("Employee ikke oprettet");
          throw new Exception ("Employee is not inserted correctly");
       }
       return(rc);
    }

	public int updateEmployee(Employee _employee)
	{
		int rc=-1;

		String query="UPDATE Employee SET "+
				"password ='"+ _employee.getPassword()+"', "+
				"accessType ='"+ _employee.getAccessType()+"', "+
				"status ='"+ _employee.getStatus()+"' "+
		        	"WHERE cpr = '"+ _employee.getCpr() + "'";
                System.out.println("Update query:" + query);
  		try{ // update employee
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}//slut try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in Employee db: "+ex);
	  	}
		return(rc);
	}
	
	public boolean deleteEmployee(String _cpr)
	{
               //int rc=-1;
		boolean succeeded = false;
	  
	  	String query="DELETE FROM Employee WHERE cpr = '" +
				_cpr + "'";
                System.out.println(query);
	  	try{ // delete from employee
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	stmt.executeUpdate(query);
	 	  	stmt.close();
	 	  	succeeded = true;
  		}//slut try	
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in Employee db: "+ex);
   	        }
		return(succeeded);
	}
	
	//private methods
    //michWere is used whenever we want to select more than one employee
	
	
	private ArrayList<Employee> miscWhere(String wClause, boolean retrieveAssociation)
	{
            ResultSet results;
	    ArrayList<Employee> list = new ArrayList<Employee>();	
		
	    String query =  buildQuery(wClause);
  
            try{ // read the employee from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
	
		while( results.next() ){
	     	Employee employee = new Employee();
		 employee = buildEmployee(results);	
                 list.add(employee);	
		}//end while
                 stmt.close();     
		}//slut try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
	//Singelwhere is used when we only select one employee 	
	private Employee singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Employee employee = new Employee();
                
	        String query =  buildQuery(wClause);
                System.out.println(query);
		try{ // read the EmployeeGroup from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            employee = buildEmployee(results);
                            //assocaition is to be build
                            stmt.close();
                            /*if(retrieveAssociation)
                            {   //The supervisor and department is to be build as well
                                String superssn = empObj.getSupervisor().getSsn();
                                Employee superEmp = singleWhere(" ssn = '" + superssn + "'",false);
                                empObj.setSupervisor(superEmp);
                                System.out.println("Supervisor is seleceted");
                               // here the department has to be selected as well
                                int dno = empObj.getDept().getDnumber();
                                
                                Department department = dbDept.findDepartment(dno, false);
                                
                                empObj.setDepartment(department);
                                System.out.println("Department is selected");
                           
                            }*/
			}
                        else{ //no employee was found
                            employee = null;
                        }
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return employee;
	}
	//method to build the query
	private String buildQuery(String wClause)
	{
	    String query="SELECT cpr, password, accessType, status  FROM Employee";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
	//method to build an employee object
	public Employee buildEmployee(ResultSet results)
      {   Employee employee = new Employee();
          try{ // the columns from the table emplayee  are used
        	  	employee.setCpr(results.getString("cpr"));
        	  	employee.setPassword(results.getString("password"));
        	  	employee.setAccessType(results.getString("accessType"));
        	  	employee.setStatus(results.getString("status"));
        	  	Person person = dbPerson.getPersonByCpr(results.getString("cpr"), false);
        	 	employee.setName(person.getName());
        	  	employee.setAddress(person.getAddress());
        	  	employee.setZipCode(person.getZipCode());
        	  	employee.setCity(person.getCity());
        	  	employee.setPhone(person.getPhone());
        	  	employee.setGsm(person.getGsm());
        	  	employee.setEmail(person.getEmail());
          }
         catch(Exception e)
         {
             System.out.println("error in building the Employee object");
         }
         return employee;
      }

 
}  
    

